﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml.Spreadsheet;
using ShExcelLib.Class;

namespace ShExcelLib
{
    public class ExcelReader
    {
        public ExcelReader(string filePath)
        {
            using (var fs = new FileStream(filePath, FileMode.Open))
            {
                var ms = new System.IO.MemoryStream();
                fs.CopyTo(ms);
                OpenExcel(ms);
            }
        }
        public ExcelReader(Stream fileStream)
        {
            OpenExcel(fileStream);
        }

        private System.IO.Compression.ZipArchive zip = null;
        private Dictionary<string, Class.Relationship> Relationships { get; set; }


        private void OpenExcel(Stream stream)
        {
            zip = new System.IO.Compression.ZipArchive(stream);
            LoadRelationships();
            LoadBook();
            LoadStyle();
            LoadSharedStrings();
        }

        private Stream OpenZipStream(string pathName)
        {
            var ent = zip.GetEntry(pathName);
            return ent.Open();
        }

        private System.Xml.XmlDocument LoadXmlContent(string path)
        {
            using (var zipStream = OpenZipStream(path))
            {
                using (var s = new StreamReader(zipStream))
                {
                    var str = s.ReadToEnd();
                    var xml = new System.Xml.XmlDocument();
                    xml.LoadXml(str);
                    return xml;
                }
            }
        }

        private void LoadRelationships()
        {
            this.Relationships = new Dictionary<string, Class.Relationship>();
            var path = "xl/_rels/workbook.xml.rels";
            var xml = LoadXmlContent(path);
            foreach (System.Xml.XmlNode item in xml.DocumentElement.ChildNodes)
            {
                var r = new Class.Relationship
                {
                    Id = item.Attributes["Id"].Value,
                    Target = item.Attributes["Target"].Value
                };
                Relationships[r.Id] = r;
            }
        }

        /// <summary>
        /// Excel所包含的子表引用
        /// </summary>
        public Class.SheetRef[] SheetRefs { get; private set; }
        private void LoadBook()
        {
            var xml = LoadXmlContent("xl/workbook.xml");
            var sheets = xml.DocumentElement["sheets"];
            List<Class.SheetRef> list = new List<Class.SheetRef>();
            string nameSpace = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";
            foreach (System.Xml.XmlElement sheet in sheets.ChildNodes)
            {
                var item = new Class.SheetRef
                {
                    Name = sheet.Attributes["name"].Value,
                    SheetId = sheet.Attributes["sheetId"].Value,
                    RId = sheet.Attributes["id", nameSpace].Value
                };
                list.Add(item);
            }
            SheetRefs = list.ToArray();
        }
        private string[] SharedStrings { get; set; }
        private void LoadSharedStrings()
        {
            var path = "xl/sharedStrings.xml";
            var xml = LoadXmlContent(path);
            List<string> strs = new List<string>();
            foreach (System.Xml.XmlNode item in xml.DocumentElement.ChildNodes)
            {
                strs.Add(item.InnerText);
            }
            SharedStrings = strs.ToArray();
        }

        private Stylesheet StyleSheet;
        private void LoadStyle()
        {
            var path = "xl/styles.xml";
            List<NumberingFormat> listNumFmt = new List<NumberingFormat>();
            using (var s = OpenZipStream(path))
            {
                var pr = DocumentFormat.OpenXml.OpenXmlPartReader.Create(s) as DocumentFormat.OpenXml.OpenXmlPartReader;
                while (pr.Read())
                {
                    if (pr.LocalName == "styleSheet")
                    {
                        StyleSheet = pr.LoadCurrentElement() as Stylesheet;
                    }
                }
            }
        }

        public void Read(ReadConfig config, Action<Newtonsoft.Json.Linq.JObject> callback)
        {
            var rls = GetSheetRelationship(config);
            if (rls == null) throw new Exception("找不到指定的表");
            var path = "xl/" + rls.Target;
            using (var s = OpenZipStream(path))
            {
                var oxr = DocumentFormat.OpenXml.OpenXmlDomReader.Create(s);
                if(config.ColumnNames==null && config.HeadRowNum > 0)
                {
                    //读取表头
                    while (oxr.Read())
                    {
                        if (oxr.LocalName == "row")
                        {
                            Row row = oxr.LoadCurrentElement() as Row;
                            if (row.RowIndex > config.HeadRowNum) throw new Exception("找不到指定的表头行");
                            if (row.RowIndex != config.HeadRowNum) continue;
                            config.ColumnNames = LoadHeadRow(row,config);
                            break;
                        }
                    }
                }

                while (oxr.Read())
                {
                    if (oxr.LocalName == "row")
                    {
                        Row row = oxr.LoadCurrentElement() as Row;
                        //跳过非数据行
                        if (row.RowIndex < config.DataRowNum) continue;
                        var json = ToJson(row, config);
                        if (json == null) continue;
                        callback(json);
                    }
                }
            }
        }

        private string[] LoadHeadRow(Row row,ReadConfig config)
        {
            var maxIndex = 0;
            var list = new List<Tuple<int, string>>();
            foreach(Cell cell in row.ChildElements)
            {
                string v = GetCellValue(cell,config);
                if (string.IsNullOrEmpty(v)) continue;
                int index = Class.ColumnConvert.ColumnIndex(cell.CellReference.Value);
                if (maxIndex < index) maxIndex = index;
                list.Add(System.Tuple.Create(index, v));
            }
            string[] cols = new string[maxIndex+1];
            for (var i = 0; i <= maxIndex; i++) cols[i] = "";
            foreach (var item in list) cols[item.Item1] = item.Item2;
            return cols;
        }

        private Newtonsoft.Json.Linq.JObject ToJson(Row row, ReadConfig config)
        {
            Newtonsoft.Json.Linq.JObject json = new Newtonsoft.Json.Linq.JObject();
            foreach (Cell cell in row.ChildElements)
            {
                var colIndex = Class.ColumnConvert.ColumnIndex(cell.CellReference.Value);
                string fn = cell.CellReference.Value;
                if (config.ColumnNames != null)
                {
                    if (colIndex >= config.ColumnNames.Length) continue;
                    fn = config.ColumnNames[colIndex];
                }
                if (string.IsNullOrEmpty(fn)) continue;
                string v = GetCellValue(cell,config);
                json[fn] = v;
            }
            return json;
        }

        private string GetCellValue(Cell cell,ReadConfig config)
        {
            string type = cell.DataType;
            string cv = cell.CellValue?.InnerText;
            switch (type)
            {
                case "inlineStr":
                    //内嵌的字符串
                    return cell.InlineString.Text.InnerText;
                case "s":
                    //sharedString
                    int index = int.Parse(cv);
                    return SharedStrings[index];
                case "n":
                //number
                case "b":
                    //bool
                    return cv;
                case "d":
                    //date
                    break;
                case "str":
                    //string
                    break;
                case "e":
                    //error
                    break;
            }
            if (string.IsNullOrEmpty(cv)) return cv;
            if (IsDateFmt(cell))
            {
                var date = new ExcelNumberFormat.ExcelDateTime(double.Parse(cv), false);
                var dt = date.AdjustedDateTime;
                string fmt = dt.TimeOfDay.TotalSeconds == 0 ? config.DateFormat : $"{config.DateFormat} {config.TimeFormat}";
                return dt.ToString(fmt);
            }
            return cell.CellValue?.InnerText;
        }

        /// <summary>
        /// 检查单元格是否指定了日期的格式
        /// </summary>
        /// <param name="c"></param>
        /// <returns></returns>
        private bool IsDateFmt(Cell c)
        {
            if (c == null) return false;
            if (c.CellValue == null) return false;
            if (string.IsNullOrEmpty(c.CellValue?.InnerText)) return false;
            if (!c.StyleIndex.HasValue) return false;
            int idx = (int)c.StyleIndex.Value;
            var cfmt = StyleSheet.CellFormats.ChildElements[idx] as CellFormat;

            if (cfmt.NumberFormatId == null) return false;
            if (!cfmt.NumberFormatId.HasValue) return false;
            int fmtId = (int)cfmt.NumberFormatId.Value;
            if (FormatCodes.IsDateFormat(fmtId)) return true;
            foreach (NumberingFormat fmt in StyleSheet.NumberingFormats)
            {
                if(fmt.NumberFormatId.Value == fmtId)
                {
                    var fmtCode = fmt.FormatCode.Value;
                    if (string.IsNullOrWhiteSpace(fmtCode)) return false;
                    fmtCode = fmtCode.ToLower();

                    if (fmtCode.Contains('#')) return false;
                    //把d的判断移到末尾，因为数字格式中的 Red 配置用到了 d
                    if (fmtCode.Contains('y') || fmtCode.Contains('m') || 
                    fmtCode.Contains('h') || fmtCode.Contains('s') || fmtCode.Contains('a'))
                        return true;
                    if (fmtCode.Contains('0') || fmtCode.Contains('¥') || fmtCode.Contains('$')) return false;
                    if (fmtCode.Contains('d')) return true;
                }
            }
            return false;
        }

        private Class.Relationship GetSheetRelationship(ReadConfig cfg)
        {
            try
            {
                //通过名称查找
                if (!string.IsNullOrEmpty(cfg.SheetName))
                {
                    foreach (var item in SheetRefs)
                    {
                        if (item.Name == cfg.SheetName)
                        {
                            return Relationships[item.RId];
                        }
                    }
                }

                //通过index查找
                if (cfg.SheetIndex < 0) return null;
                var sr = SheetRefs[cfg.SheetIndex];
                return Relationships[sr.RId];
            }
            catch {
                return null;
            }
        }
    }
}
